using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Data;
using System.Xml;
using System.IO;

namespace Destra.Tools.DACGenerator
{
    /// <summary>
    /// Retrieves information regarding the specified SQL query 
    /// using the SQL Server 2005 XML query plan mechanism as well
    /// as the SchemaOnly command behaviour (SET FMTONLY ON).
    /// </summary>
    public class QueryInfo
    {


        #region Predicates

        public class Predicates
        {

            public Predicate<Destra.Tools.DACGenerator.QueryInfo.Parameter> FindParameterByName(string columnName)
            {
                return delegate(Destra.Tools.DACGenerator.QueryInfo.Parameter e)
                {
                    return e.Name.Replace(",", "").Replace("@", "").Trim().ToLowerInvariant().Equals(columnName.Replace(",", "").Replace("@", "").Trim().ToLowerInvariant());
                };
            }

        }

        #endregion

        #region OutputColumn

        public class OutputColumn
        {
            public string Name;
            public Type Type;
            public bool Nullable;

            public OutputColumn(string name, Type type, bool nullable)
            {
                this.Name = name;
                this.Type = type;
                this.Nullable = nullable;
            }
        }

        #endregion

        #region Parameter

        public class Parameter
        {
            public string Name;
            public SqlDbType SqlDbType;
            public Type Type;
            public bool Nullable;
            public bool CommandOutputVariable;

            public Parameter()
            {

            }

            public Parameter(string name, SqlDbType sqlDbType, Type type, bool nullable)
            {
                this.Name = name;
                this.SqlDbType = sqlDbType;
                this.Type = type;
                this.Nullable = nullable;
            }

            public string FormattedName
            {
                get
                {
                    char[] chars = this.Name.TrimStart("@".ToCharArray()).ToCharArray();

                    if (chars.Length > 0)
                    {
                        chars[0] = char.ToLower(chars[0]);
                    }

                    return new string(chars);
                }
            }
        }

        #endregion

        #region QueryTables Class

        [Serializable()]
        public class QueryTables
        {

            #region Serialization

            public string Serialize()
            {
                using (MemoryStream stream = new MemoryStream())
                {
                    System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(this.GetType());
                    serializer.Serialize(stream, this);

                    stream.Flush();
                    stream.Seek(0, SeekOrigin.Begin);

                    using (StreamReader reader = new StreamReader(stream))
                    {
                        return reader.ReadToEnd();
                    }
                }
            }

            #endregion

            private List<Table> _dependencies = new List<Table>();
            private List<string> _dependenciesKey = new List<string>();

            public void AddDependency(Table dependency)
            {
                if (!_dependenciesKey.Contains(dependency.Inline))
                {
                    _dependenciesKey.Add(dependency.Inline);
                    _dependencies.Add(dependency);
                }
            }

            public List<Table> Dependencies
            {
                get { return _dependencies; }
                set
                {
                    //_dependencies = value; 
                }
            }


            public class Table
            {

                public Table()
                {
                }

                public Table(string schema, string tableName)
                {
                    Schema = schema;
                    TableName = tableName;
                    Inline = Schema.ToUpperInvariant() + "." + TableName.ToUpperInvariant();
                }

                [System.Xml.Serialization.XmlAttribute("Schema")]
                public string Schema;

                [System.Xml.Serialization.XmlAttribute("Table")]
                public string TableName;

                [System.Xml.Serialization.XmlAttribute("Inline")]
                public string Inline;

            }
        }

        #endregion

        private string m_connectionString;
        private string m_sql;

        public QueryInfo(string connectionString, string sql)
        {
            m_connectionString = connectionString;
            m_sql = sql;
        }

        public static Type SqlDbTypeToSystemType(SqlDbType sqlDbType)
        {
            Type type = null;

            switch (sqlDbType)
            {
                case SqlDbType.BigInt:
                    type = typeof(Int64);
                    break;
                case SqlDbType.Binary:
                    type = typeof(byte[]);
                    break;
                case SqlDbType.Bit:
                    type = typeof(bool);
                    break;
                case SqlDbType.Char:
                    type = typeof(string);
                    break;
                case SqlDbType.DateTime:
                    type = typeof(DateTime);
                    break;
                case SqlDbType.Decimal:
                    type = typeof(decimal);
                    break;
                case SqlDbType.Float:
                    type = typeof(double);
                    break;
                case SqlDbType.Image:
                    type = typeof(byte[]);
                    break;
                case SqlDbType.Int:
                    type = typeof(Int32);
                    break;
                case SqlDbType.Money:
                    type = typeof(decimal);
                    break;
                case SqlDbType.NChar:
                    type = typeof(string);
                    break;
                case SqlDbType.NText:
                    type = typeof(string);
                    break;
                case SqlDbType.NVarChar:
                    type = typeof(string);
                    break;
                case SqlDbType.Real:
                    type = typeof(Single);
                    break;
                case SqlDbType.SmallDateTime:
                    type = typeof(DateTime);
                    break;
                case SqlDbType.SmallInt:
                    type = typeof(Int16);
                    break;
                case SqlDbType.SmallMoney:
                    type = typeof(decimal);
                    break;
                case SqlDbType.Text:
                    type = typeof(string);
                    break;
                case SqlDbType.Timestamp:
                    type = typeof(byte[]);
                    break;
                case SqlDbType.TinyInt:
                    type = typeof(byte);
                    break;
                case SqlDbType.Udt:
                    type = typeof(object);
                    break;
                case SqlDbType.UniqueIdentifier:
                    type = typeof(Guid);
                    break;
                case SqlDbType.VarBinary:
                    type = typeof(byte[]);
                    break;
                case SqlDbType.VarChar:
                    type = typeof(string);
                    break;
                case SqlDbType.Variant:
                    type = typeof(object);
                    break;
                case SqlDbType.Xml:
                    type = typeof(string);
                    break;
                default:
                    throw new ApplicationException(
                        string.Format("Missing converter for SqlDbType '{0}'", sqlDbType.ToString()));
            }

            return type;
        }

        private List<OutputColumn> GetOutputColumns(List<Parameter> parameters)
        {
            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                connection.Open();

                // convert the text/ntext parameters to varchar because t-sql doesn't like text and ntext parameters
                List<Parameter> safeTypeParameters = new List<Parameter>();
                foreach (Parameter parameter in parameters)
                {
                    switch (parameter.SqlDbType)
                    {
                        case SqlDbType.Text:
                        case SqlDbType.NText:
                            safeTypeParameters.Add(new Parameter(parameter.Name, SqlDbType.VarChar, typeof(string), parameter.Nullable));
                            break;
                        default:
                            safeTypeParameters.Add(new Parameter(parameter.Name, parameter.SqlDbType, parameter.Type, parameter.Nullable));
                            break;
                    }
                }

                SqlCommand command = new SqlCommand();
                command.CommandText = GetSqlText(safeTypeParameters);
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                List<OutputColumn> outputColumns = new List<OutputColumn>();

                using (SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                    DataTable dataTable = dataReader.GetSchemaTable();

                    if (dataTable != null) // will be null if the query has no output columns
                    {
                        foreach (DataRow dataRow in dataTable.Rows)
                        {
                            outputColumns.Add(
                                new OutputColumn(
                                    (string)dataRow["ColumnName"],
                                    (Type)dataRow["DataType"],
                                    (bool)dataRow["AllowDBNull"])
                                    );
                        }
                    }
                }

                return outputColumns;
            }
        }

        private string GetParameterName(string message)
        {
            Regex regex = new Regex("\"@(?<name>.*)\"");
            Match match = regex.Match(message);
            if (match.Success == true)
            {
                return "@" + match.Groups["name"].Value;
            }
            else
            {
                throw new ApplicationException(
                    string.Format("Could not get parameter name from '{0}'",
                        message));
            }
        }

        private Parameter FindByName(List<Parameter> parameters, string name)
        {
            Parameter param = null;

            foreach (Parameter parameter in parameters)
            {
                if (parameter.Name.Equals(name))
                {
                    param = parameter;
                }
            }

            return param;
        }

        private string GetSqlText(List<Parameter> parameters)
        {
            StringBuilder builder = new StringBuilder();

            foreach (Parameter parameter in parameters)
            {
                builder.AppendFormat("declare {0} {1}\r\n", parameter.Name, parameter.SqlDbType);
            }
            builder.Append(m_sql);

            return builder.ToString();
        }

        private QueryTables GetQueryTables()
        {
            QueryTables queryTables = new QueryTables();

            List<Parameter> parameters = new List<Parameter>();

            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.CommandText = "SET SHOWPLAN_ALL ON";
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                command.ExecuteNonQuery();

                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                bool parameterError;

                do
                {
                    parameterError = false;

                    try
                    {
                        command.CommandText = GetSqlText(parameters);
                        command.Connection = connection;

                        DataTable queryPlanTable = new DataTable();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(queryPlanTable);
                        }

                        foreach (DataRow row in queryPlanTable.Rows)
                        {
                            if (row["Argument"] != DBNull.Value)
                            {
                                string argument = row["Argument"].ToString();
                                if (argument.StartsWith("OBJECT:"))
                                {
                                    argument = argument.Replace("OBJECT:", "").Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "");
                                    if (argument.Contains(", "))
                                    {
                                        argument = argument.Substring(0, argument.IndexOf(", "));
                                    }

									string []ArgParts = argument.Split(".".ToCharArray());

									// There was a problem where inserting something into a table variable
									// was coming up here as a table dependency. As a result the "argument"
									// variable only had @VariableName in it instead of the expected text
									// to look something like "mp3.dbo.KioskReplication.PK_HNReplication AS KR"
									if (ArgParts.Length > 2)
									{
										queryTables.AddDependency(new QueryTables.Table(
											argument.Split(".".ToCharArray())[1], argument.Split(".".ToCharArray())[2]));
									}
                                }
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (e.Errors[0].Number == 137)
                        {
                            parameterError = true;

                            string parameterName = GetParameterName(e.Errors[0].Message);

                            Parameter parameter = new Parameter();
                            parameter.Name = parameterName;
                            parameter.SqlDbType = SqlDbType.VarChar;
                            parameters.Add(parameter);
                        }
                        else if (e.Errors[0].Number == 1060)
                        {
                            parameterError = true;

                            string line = string.Empty;

                            using (StringReader stringReader = new StringReader(command.CommandText))
                            {
                                for (int i = 0; i < e.LineNumber; i++)
                                {
                                    line = stringReader.ReadLine();
                                }
                            }

                            Regex regex = new Regex(@"\(@(.*)\)");
                            Match match = regex.Match(line);
                            if (match.Success)
                            {
                                bool foundTop = false;

                                foreach (Parameter parameter in parameters)
                                {
                                    foreach (Group group in match.Groups)
                                    {
                                        if (parameter.Name.Equals("@" + group.Value))
                                        {
                                            parameter.SqlDbType = SqlDbType.Int;

                                            foundTop = true;
                                            break;
                                        }
                                    }

                                    if (foundTop == true)
                                    {
                                        break;
                                    }
                                }

                                if (foundTop == false)
                                {
                                    throw e;
                                }
                            }
                            else
                            {
                                throw e;
                            }
                        }
                        else
                        {
                            throw e;
                        }
                    }
                }
                while (parameterError == true);
            }



            return queryTables;
        }

        private List<Parameter> GetParameters()
        {
            List<Parameter> parameters = new List<Parameter>();

            using (SqlConnection connection = new SqlConnection(m_connectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.CommandText = "SET SHOWPLAN_XML ON";
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                command.ExecuteNonQuery();

                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                XmlDocument queryPlan = new XmlDocument();
                bool parameterError;

                do
                {
                    parameterError = false;

                    try
                    {
                        command.CommandText = GetSqlText(parameters);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                queryPlan = new XmlDocument();
                                queryPlan.LoadXml((string)reader[0]);
                            }
                            else
                            {
                                throw new ApplicationException("Could not get query plan.");
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (e.Errors[0].Number == 137)
                        {
                            parameterError = true;

                            string parameterName = GetParameterName(e.Errors[0].Message);

                            Parameter parameter = new Parameter();
                            parameter.Name = parameterName;
                            parameter.SqlDbType = SqlDbType.VarChar;
                            parameters.Add(parameter);
                        }
                        else if (e.Errors[0].Number == 1060)
                        {
                            parameterError = true;

                            string line = string.Empty;

                            using (StringReader stringReader = new StringReader(command.CommandText))
                            {
                                for (int i = 0; i < e.LineNumber; i++)
                                {
                                    line = stringReader.ReadLine();
                                }
                            }

                            Regex regex = new Regex(@"\(@(.*)\)");
                            Match match = regex.Match(line);
                            if (match.Success)
                            {
                                bool foundTop = false;

                                foreach (Parameter parameter in parameters)
                                {
                                    foreach (Group group in match.Groups)
                                    {
                                        if (parameter.Name.Equals("@" + group.Value))
                                        {
                                            parameter.SqlDbType = SqlDbType.Int;

                                            foundTop = true;
                                            break;
                                        }
                                    }

                                    if (foundTop == true)
                                    {
                                        break;
                                    }
                                }

                                if (foundTop == false)
                                {
                                    throw e;
                                }
                            }
                            else
                            {
                                throw e;
                            }
                        }
                        else
                        {
                            throw e;
                        }
                    }
                }
                while (parameterError == true);

                XmlNamespaceManager namespaceManager = new XmlNamespaceManager(queryPlan.NameTable);
                namespaceManager.AddNamespace("sql", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");

                // set the type each parameter
                foreach (Parameter parameter in parameters)
                {
                    parameter.Type = SqlDbTypeToSystemType(parameter.SqlDbType);
                }

                // use the Convert elements to set the actual type of the corresponding parameters
                XmlNodeList convertNodes = queryPlan.SelectNodes("//sql:Convert", namespaceManager);
                foreach (XmlNode convertNode in convertNodes)
                {
                    XmlNodeList columnReferenceNodes = convertNode.SelectNodes(".//sql:ColumnReference", namespaceManager);
                    foreach (XmlNode columnReferenceNode in columnReferenceNodes)
                    {
                        Parameter parameter = FindByName(parameters, columnReferenceNode.Attributes["Column"].Value);
                        if (parameter != null)
                        {
                            string dataType = convertNode.Attributes["DataType"].Value;

                            if (dataType.EndsWith("(max)", StringComparison.InvariantCultureIgnoreCase))
                            {
                                dataType = dataType.Substring(0, dataType.Length - "(max)".Length);
                            }

                            parameter.SqlDbType = (SqlDbType)Enum.Parse(
                                typeof(SqlDbType),
                                dataType,
                                true);

                            parameter.Type = SqlDbTypeToSystemType(parameter.SqlDbType);
                        }
                    }
                }

                // use the Assign elements to determine if the corresponding parameters are nullable
                XmlNodeList queryPlanNodes = queryPlan.SelectNodes("//sql:QueryPlan", namespaceManager);
                foreach (XmlNode queryPlanNode in queryPlanNodes)
                {
                    XmlNodeList assignNodes = queryPlanNode.SelectNodes(".//sql:Assign", namespaceManager);
                    foreach (XmlNode assignNode in assignNodes)
                    {
                        XmlNode columnReferenceNode = assignNode.SelectSingleNode(".//sql:Identifier/sql:ColumnReference", namespaceManager);

                        if (columnReferenceNode != null)
                        {
                            Parameter parameter = FindByName(parameters, columnReferenceNode.Attributes["Column"].Value);

                            if (parameter == null)
                            {
                                // see if there is a matching DefinedValue
                                XmlNode definedValueColumnReferenceNode = queryPlanNode.SelectSingleNode(
                                    string.Format(".//sql:DefinedValue/sql:ColumnReference[@Column='{0}']",
                                        columnReferenceNode.Attributes["Column"].Value),
                                    namespaceManager);

                                XmlNode definedValueNode = (definedValueColumnReferenceNode == null) ?
                                    null : definedValueColumnReferenceNode.ParentNode;

                                if (definedValueNode != null)
                                {
                                    XmlNode definedColumnReferenceNode = definedValueNode.SelectSingleNode(".//sql:Identifier/sql:ColumnReference", namespaceManager);

                                    if (definedColumnReferenceNode != null)
                                    {
                                        parameter = FindByName(parameters, definedColumnReferenceNode.Attributes["Column"].Value);

                                        if (parameter != null)
                                        {
                                            parameter.Nullable = (assignNode.SelectSingleNode(".//sql:Intrinsic[@FunctionName='RaiseIfNull']", namespaceManager) == null);
                                        }
                                    }
                                }
                            }
                            else
                            {
                                parameter.Nullable = (assignNode.SelectSingleNode(".//sql:Intrinsic[@FunctionName='RaiseIfNull']", namespaceManager) == null);
                            }
                        }
                    }
                }
            }

            return parameters;
        }

        public void Load(out List<Parameter> parameters, out List<OutputColumn> outputColumns, out QueryTables queryTables)
        {
            parameters = GetParameters();
            queryTables = GetQueryTables();
            outputColumns = GetOutputColumns(parameters);
        }
    }
}
